﻿using System.Collections.Generic;
using System.Data.SqlClient;

namespace Helper.DalHelper
{
    public enum LikeParams
    {
        StartWith,
        EndWith,
        Include
    }

    public static class SqlCommandExtension
    {
        public static SqlCommand Where(this SqlCommand pCommand)
        {
            pCommand.CommandText += " WHERE ";
            return pCommand;
        }

        public static SqlCommand And(this SqlCommand pCommand)
        {
            pCommand.CommandText += " AND ";
            return pCommand;
        }

        public static SqlCommand Equal(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            pCommand.Parameters.Add(pParameter);

            pCommand.CommandText += string.Format(" ({0} = {1}) ", pColumnName, pParameter.ParameterName);

            return pCommand;
        }

        public static SqlCommand NotEqual(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            pCommand.CommandText += string.Format(" ({0} <> {1}) ", pColumnName, pParameter.ParameterName);

            pCommand.Parameters.Add(pParameter);

            return pCommand;
        }

        public static SqlCommand Or(this SqlCommand pCommand, string pFirstColumnName, SqlParameter pFirstParameter, string pSecondColumnName, SqlParameter pSecondParameter)
        {
            if (pCommand.Parameters.Contains(pFirstParameter.ParameterName)) return pCommand;

            if (pCommand.Parameters.Contains(pSecondParameter.ParameterName)) return pCommand;

            if (pFirstParameter.ParameterName.Equals(pSecondParameter.ParameterName)) return pCommand;

            pCommand.CommandText += string.Format(" (({0} = {1}) OR ({2} = {3})) ", pFirstColumnName, pFirstParameter.ParameterName, pSecondColumnName, pSecondParameter.ParameterName);

            pCommand.Parameters.Add(pFirstParameter);

            pCommand.Parameters.Add(pSecondParameter);

            return pCommand;
        }

        public static SqlCommand GreaterThenOrEqual(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            pCommand.CommandText += string.Format(" ({0} >= {1}) ", pColumnName, pParameter.ParameterName);

            pCommand.Parameters.Add(pParameter);

            return pCommand;
        }

        public static SqlCommand LessThenOrEqual(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            pCommand.CommandText += string.Format(" ({0} <= {1}) ", pColumnName, pParameter.ParameterName);

            pCommand.Parameters.Add(pParameter);

            return pCommand;
        }

        public static SqlCommand Between(this SqlCommand pCommand, string pColumnName, SqlParameter pParameterStartRange, SqlParameter pParameterEndRange)
        {
            if (pCommand.Parameters.Contains(pParameterStartRange.ParameterName)) return pCommand;

            if (pCommand.Parameters.Contains(pParameterEndRange.ParameterName)) return pCommand;

            if (pParameterStartRange.ParameterName.Equals(pParameterEndRange.ParameterName)) return pCommand;

            pCommand.CommandText += string.Format(" ({0} BETWEEN {1} AND {2}) ", pColumnName, pParameterStartRange.ParameterName, pParameterEndRange.ParameterName);

            pCommand.Parameters.Add(pParameterStartRange);

            pCommand.Parameters.Add(pParameterEndRange);

            return pCommand;
        }

        public static void GetLikeSymbol(LikeParams pPrms, ref string pPerc1, ref string pPerc2)
        {
            switch (pPrms)
            {
                case LikeParams.StartWith:
                    pPerc1 = "'%' +";
                    break;
                case LikeParams.EndWith:
                    pPerc2 = "+ '%'";
                    break;
                case LikeParams.Include:
                    pPerc1 = "'%' +";
                    pPerc2 = "+ '%'";
                    break;
                default:
                    break;
            }
        }

        public static SqlCommand Like(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter, LikeParams pPrms)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            string perc1 = string.Empty;

            string perc2 = string.Empty;

            GetLikeSymbol(pPrms, ref perc1, ref perc2);

            pCommand.CommandText += string.Format(" ({0} LIKE {2} {1} {3}) ", pColumnName, pParameter.ParameterName, perc1, perc2);

            pCommand.Parameters.Add(pParameter);

            return pCommand;
        }

        public static SqlCommand NotLike(this SqlCommand pCommand, string pColumnName, SqlParameter pParameter, LikeParams pPrms)
        {
            if (pCommand.Parameters.Contains(pParameter.ParameterName)) return pCommand;

            string perc1 = string.Empty;

            string perc2 = string.Empty;

            GetLikeSymbol(pPrms, ref perc1, ref perc2);

            pCommand.CommandText += string.Format(" ({0} NOT LIKE {2} {1} {3}) ", pColumnName, pParameter.ParameterName, perc1, perc2);

            pCommand.Parameters.Add(pParameter);

            return pCommand;
        }

        public static SqlCommand In(this SqlCommand pCommand, string pColumnName, string pValuesString)
        {
            pCommand.CommandText += string.Format(" ({0} IN ({1})) ", pColumnName, pValuesString);

            return pCommand;
        }

        public static SqlCommand OrderBy(this SqlCommand pCommand, IEnumerable<string> pColumnNames)
        {
            pCommand.CommandText += "  ORDER BY ";

            foreach (string pColumnName in pColumnNames)
            {
                pCommand.CommandText += string.Format("{0},", pColumnName);
            }
            pCommand.CommandText = pCommand.CommandText.Remove(pCommand.CommandText.Length - 1);
            return pCommand;
        }

        public static SqlCommand GroupBy(this SqlCommand pCommand, IEnumerable<string> pColumnNames)
        {
            pCommand.CommandText += "  GROUP BY ";
            foreach (string pColumnName in pColumnNames)
            {
                pCommand.CommandText += string.Format("{0},", pColumnName);
            }
            pCommand.CommandText = pCommand.CommandText.Remove(pCommand.CommandText.Length - 1);
            return pCommand;
        }
    }
}
